package com.cbs.database;

import android.annotation.TargetApi;
import android.content.Context;
import android.content.res.Resources;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Build;
import com.cbs.log.L;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.*;

/**
 * Created by brochexu on 9/30/14.
 */
public class SqliteDatabase extends SQLiteOpenHelper implements Database {
    private static final String TAG = SqliteDatabase.class.getName();
    private boolean debug = false;
    private Context context;
    private int createResourceId = -1;
    private Hashtable<Integer, Integer> upgradeResourceIds = new Hashtable<>();
    private final Object lock = new Object();

    public SqliteDatabase(Context context, String name, int version) {
        super(context, name, null, version);
        this.context = context;
    }

    public SqliteDatabase setCreateResource(int resId) {
        createResourceId = resId;
        return this;
    }

    public SqliteDatabase addUpgradeResource(int oldVersion, int resId) {
        upgradeResourceIds.put(oldVersion, resId);
        return this;
    }

    public SqliteDatabase setDebug(boolean debug) {
        this.debug = debug;
        return this;
    }

    @TargetApi(Build.VERSION_CODES.HONEYCOMB)
    public synchronized List<Map<String, Object>> query(String sql) {
        List<Map<String, Object>> results = new ArrayList<>();
        synchronized (lock) {
            SQLiteDatabase database = this.getReadableDatabase();
            Cursor cursor = database.rawQuery(sql, null);
            int columnCount = cursor.getColumnCount();
            String[] columnNames = new String[columnCount];
            for (int i = 0; i < columnCount; i++) {
                columnNames[i] = cursor.getColumnName(i);
            }
            while (cursor.moveToNext()) {

                HashMap<String, Object> item = new HashMap<>();
                for (int i = 0; i < columnCount; i++) {
                    item.put(columnNames[i], getObject(cursor, i));
                }
                results.add(item);
            }
            cursor.close();
            database.close();

            // print debug info
            printDebugInfo(sql);
        }
        return results;
    }

    @TargetApi(Build.VERSION_CODES.HONEYCOMB)
    public synchronized Map<String, Object> fetch(String sql) {
        Map<String, Object> result = null;
        synchronized (lock) {
            SQLiteDatabase database = this.getReadableDatabase();
            Cursor cursor = database.rawQuery(sql, null);
            int columnCount = cursor.getColumnCount();
            String[] columnNames = new String[columnCount];
            for (int i = 0; i < columnCount; i++) {
                columnNames[i] = cursor.getColumnName(i);
            }
            if (cursor.moveToNext()) {
                result = new HashMap<>();
                for (int i = 0; i < columnCount; i++) {
                    result.put(columnNames[i], getObject(cursor, i));
                }
            }
            cursor.close();
            database.close();

            // print debug info
            printDebugInfo(sql);
        }
        return result;
    }

    @TargetApi(Build.VERSION_CODES.HONEYCOMB)
    public synchronized Object scalar(String sql) {
        Object result = null;
        synchronized (lock) {
            SQLiteDatabase database = this.getReadableDatabase();
            Cursor cursor = database.rawQuery(sql, null);
            if (cursor.moveToNext()) {
                result = getObject(cursor, 0);
            }
            cursor.close();
            database.close();

            // print debug info
            printDebugInfo(sql);
        }
        return result;
    }

    public synchronized void execute(String sql) {
        synchronized (lock) {
            SQLiteDatabase database = getWritableDatabase();
            database.execSQL(sql);
            database.close();

            // print debug info
            printDebugInfo(sql);
        }
    }

    public synchronized int insert(String sql) {
        int id = -1;
        synchronized (lock) {
            SQLiteDatabase database = getWritableDatabase();
            database.execSQL(sql);
            Cursor cursor = database.rawQuery("select last_insert_rowid()", null);
            if (cursor.moveToNext()) {
                id = cursor.getInt(0);
            }
            cursor.close();
            database.close();

            // print debug info
            printDebugInfo(sql);
        }
        return id;
    }

    public synchronized int update(String sql) {
        execute(sql);
        return 0;
    }

    public synchronized int delete(String sql) {
        return update(sql);
    }

    public synchronized void executeBatch(List<String> sqls) {
        if (sqls == null) {
            return;
        }
        synchronized (lock) {
            SQLiteDatabase database = getWritableDatabase();
            database.beginTransaction();
            for (String sql : sqls) {
                database.execSQL(sql);
            }
            database.setTransactionSuccessful();
            database.endTransaction();

            // print debug info
            for (String sql : sqls) {
                printDebugInfo(sql);
            }
        }
    }

    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        execProfile(sqLiteDatabase, createResourceId);
    }

    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
        execProfile(sqLiteDatabase, upgradeResourceIds.get(oldVersion));
    }

    private void execProfile(SQLiteDatabase sqLiteDatabase, int resId) {
        sqLiteDatabase.beginTransaction();
        try {
            InputStream inputStream = context.getResources().openRawResource(resId);
            InputStreamReader inputStreamReader = new InputStreamReader(inputStream);
            BufferedReader bufferedReader = new BufferedReader(inputStreamReader);
            String line;
            try {
                while (null != (line = bufferedReader.readLine())) {
                    sqLiteDatabase.execSQL(line);
                }
                bufferedReader.close();
                inputStreamReader.close();
                inputStream.close();
            } catch (IOException e) {
                L.e(this.getClass().getName(), e.getMessage());
            }

        } catch (Resources.NotFoundException e) {
            L.w(TAG, "execute finished with warnings");
        }
        sqLiteDatabase.setTransactionSuccessful();
        sqLiteDatabase.endTransaction();
    }

    private void printDebugInfo(String sql) {
        if (debug) {
            L.d(TAG, sql);
        }
    }

    @TargetApi(Build.VERSION_CODES.HONEYCOMB)
    private Object getObject(Cursor cursor, int index) {
        int columnType = cursor.getType(index);
        Object res = null;
        if (columnType == Cursor.FIELD_TYPE_BLOB) {
            res = cursor.getBlob(index);
        } else if (columnType == Cursor.FIELD_TYPE_FLOAT) {
            res = cursor.getFloat(index);
        } else if (columnType == Cursor.FIELD_TYPE_INTEGER) {
            res = cursor.getInt(index);
        } else if (columnType == Cursor.FIELD_TYPE_NULL) {
            res = null;
        } else if (columnType == Cursor.FIELD_TYPE_STRING) {
            res = cursor.getString(index);
        }
        return res;
    }
}
